﻿<%--
 * import.aspx
 * 
 * Copyright (C) 2011 Elena Shishkina
 * 
 * This file is part of ServersInventory.
 * 
 * ServersInventory is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * ServersInventory is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with ServersInventory.  If not, see <http://www.gnu.org/licenses/>.
--%>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Collections" %>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Web.Configuration" %>
<%@ Page Language="c#" ContentType="application/json" %>

<script runat="server">
    public SqlConnection DbConnect()
    {
        var str = new SqlConnectionStringBuilder();
        str.DataSource = WebConfigurationManager.AppSettings["DataSource"];
        str.UserID = WebConfigurationManager.AppSettings["UserID"];
        str.Password = WebConfigurationManager.AppSettings["Password"];
        str.InitialCatalog = WebConfigurationManager.AppSettings["InitialCatalog"];
        var conn = new SqlConnection(str.ToString());
        conn.Open();
        return conn;
    }

    public void ProcessRequest()
    {
        try
        {
            using (var conn = DbConnect())
            {
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    if (!string.IsNullOrEmpty(Request["empty"]) && Request["empty"].ToLower() == "on")
                    {
                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.Transaction = trans;
                            cmd.CommandText = "delete from server_ips; delete from inventory; delete from racks; delete from dcs; delete from vendors;";
                            cmd.ExecuteNonQuery();
                        }
                    }
                    // Read vendors
                    if (Request.Files["vendors"] != null) LoadVendors(conn, trans);
                    if (Request.Files["datacenters"] != null) LoadDatacenters(conn, trans);
                    else throw new Exception("Datacenters file hasn't provided");
                    if (Request.Files["racks"] != null) LoadRacks(conn, trans);
                    else throw new Exception("Racks file hasn't provided");
                    if (Request.Files["servers"] != null) LoadServers(conn, trans);
                    else throw new Exception("Servers file hasn't provided");
                    
                    trans.Commit();
                    Response.Write("Finished\r\n");
                }
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }

    private void LoadVendors(SqlConnection conn, SqlTransaction trans)
    {
        using (var stream = new StreamReader(Request.Files["vendors"].InputStream, Encoding.GetEncoding(1251)))
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"declare @id int;
select @id = id from vendors where name = @name;
if @id is null 
	insert into vendors (name, color) values (@name, @color);
else 
	update vendors set color = @color where name = @name;
"; 
                cmd.Transaction = trans;
                cmd.Parameters.Add("@name", SqlDbType.VarChar).Size = 256;
                cmd.Parameters.Add("@color", SqlDbType.VarChar).Size = 32;
                cmd.Prepare();
                string line;
                bool first = true;
                while ((line = stream.ReadLine()) != null)
                {
                    line = line.Trim();
                    if (line.Length == 0) continue;
                    if (first)
                    {
                        first = false;
                    }
                    else
                    {
                        var cols = line.Split(';');
                        cmd.Parameters["@name"].Value = cols[0];
                        cmd.Parameters["@color"].Value = cols[1];
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
    }

    private void LoadDatacenters(SqlConnection conn, SqlTransaction trans)
    {
        var cnt = 0;
        using (var stream = new StreamReader(Request.Files["datacenters"].InputStream, Encoding.GetEncoding(1251)))
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"declare @id int;
select @id = id from dcs where name = @name;
if @id is null 
	insert into dcs(name, address, power, notes) values (@name, @address, @power, @notes);
else 
	update dcs set address = @address, power = @power, notes = @notes where name = @name;";
                cmd.Transaction = trans;
                cmd.Parameters.Add("@name", SqlDbType.VarChar).Size = 256;
                cmd.Parameters.Add("@address", SqlDbType.VarChar).Size = 256;
                cmd.Parameters.Add("@power", SqlDbType.Int);
                cmd.Parameters.Add("@notes", SqlDbType.VarChar).Size = 4096;
                //name, address, power, notes
                cmd.Prepare();
                string line;
                bool first = true;
                while ((line = stream.ReadLine()) != null)
                {
                    line = line.Trim();
                    if (line.Length == 0) continue;
                    if (first) first = false;
                    else
                    {
                        cnt++;
                        var cols = line.Split(';');
                        if (cols[0].Length == 0) throw new Exception("Empty name in string: " + line);
                        cmd.Parameters["@name"].Value = cols[0];
                        cmd.Parameters["@address"].Value = cols[1];
                        if (cols[2].Length == 0) cmd.Parameters["@power"].Value = DBNull.Value;
                        else cmd.Parameters["@power"].Value = cols[2];
                        cmd.Parameters["@notes"].Value = cols[3];
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
        if (cnt == 0) throw new Exception("Empty datacenters file");
    }

    private void LoadRacks(SqlConnection conn, SqlTransaction trans)
    {
        var cnt = 0;
        using (var stream = new StreamReader(Request.Files["racks"].InputStream, Encoding.GetEncoding(1251)))
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"
declare @id int;
declare @dc_id int;
declare @vendor_id int;

if @vendor is not null begin
    select @vendor_id = id from vendors where name = @vendor;
    if @vendor_id is null begin
	    --raiserror('Vendor %s does not exist', 16, 1, @vendor);
	    insert into vendors (name) values (@vendor);
	    select @vendor_id = @@identity;
    end;
end;

select @dc_id = id from dcs where name = @dc;
if @dc_id is null begin
	select @dc + ' not found';
end
else begin
	select @id = id from racks where dc_id = @dc_id and name = @name;
	if @id is null
		insert into racks (name, units, notes, dc_id, vendor_id, responsible) values (@name, @units, @notes, @dc_id, @vendor_id, @responsible);
	else
		update racks set units = @units, notes = @notes, vendor_id = @vendor_id, responsible = @responsible where dc_id = @dc_id and name = @name;
	select 'OK';
end;";
                cmd.Transaction = trans;
                cmd.Parameters.Add("@name", SqlDbType.VarChar).Size = 256;
                cmd.Parameters.Add("@units", SqlDbType.Int);
                cmd.Parameters.Add("@dc", SqlDbType.VarChar).Size = 256;
                cmd.Parameters.Add("@vendor", SqlDbType.VarChar).Size = 256;
                cmd.Parameters.Add("@notes", SqlDbType.VarChar).Size = 4096;
                cmd.Parameters.Add("@responsible", SqlDbType.VarChar).Size = 512;
                cmd.Prepare();
                string line;
                bool first = true;
                while ((line = stream.ReadLine()) != null)
                {
                    line = line.Trim();
                    if (line.Length == 0) continue;
                    if (first) first = false;
                    else
                    {
                        cnt++;
                        var cols = line.Split(';');
                        if (cols[0].Length == 0) throw new Exception("Empty dc_name in string: " + line);
                        if (cols[1].Length == 0) throw new Exception("Empty name in string: " + line);
                        if (cols[3].Length == 0) throw new Exception("Empty units in string: " + line);
                        //dc_name, name, vendor, units, notes
                        cmd.Parameters["@dc"].Value = cols[0];
                        cmd.Parameters["@name"].Value = cols[1];
                        if (cols[2].Length == 0) cmd.Parameters["@vendor"].Value = DBNull.Value;
                        else cmd.Parameters["@vendor"].Value = cols[2];
                        cmd.Parameters["@units"].Value = cols[3];
                        cmd.Parameters["@notes"].Value = cols[4];
                        cmd.Parameters["@responsible"].Value = cols[5];
                        var result = cmd.ExecuteScalar().ToString();
                        if (result != "OK") throw new Exception(result);
                    }
                }
            }
        }
        if (cnt == 0) throw new Exception("Empty racks file");
    }

    private void LoadServers(SqlConnection conn, SqlTransaction trans)
    {
        var cnt = 0;
        using (var stream = new StreamReader(Request.Files["servers"].InputStream, Encoding.GetEncoding(1251)))
        {
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = @"declare @id int;
declare @dc_id int;
declare @rack_id int;
declare @vendor_id int;

if @vendor is not null begin
    select @vendor_id = id from vendors where name = @vendor;
    if @vendor_id is null begin
	    --raiserror('Vendor %s does not exist', 16, 1, @vendor);
	    insert into vendors (name) values (@vendor);
	    select @vendor_id = @@identity;
    end;
end;

select @dc_id = id from dcs where name = @dc;
if @dc_id is null begin
	select @dc + ' not found';
end
else begin
	select @rack_id = id  from racks where dc_id = @dc_id and name = @rack;
	if @rack_id is null begin
		select @rack + ' not found in dc ' + @dc;
	end
	else begin
		select @id = id from  inventory where rack_id = @rack_id and name = @name;
		if @id is null begin		
			insert into inventory (name, model, type, sn, [in], pusage, pstate, units, mgmtip, notes, color, rack_id, vendor_id, responsible) 
			values (@name, @model, @type, @sn, @in, @pusage, @pstate, @units, @mgmtip, @notes, @color, @rack_id, @vendor_id, @responsible);
            select @id = @@identity;			
        end            
		else begin
			update inventory set model = @model, type = @type, sn = @sn, [in] = @in, pusage = @pusage, pstate = @pstate, units = @units,
			mgmtip = @mgmtip, notes = @notes, color = @color, vendor_id = @vendor_id, responsible = @responsible where rack_id = @rack_id and name = @name;
			delete from server_ips where server_id = @id;
		end
		select 'OK', @id;
	end
end;";
                cmd.Transaction = trans;
                /*
declare @type varchar(256);
declare @sn varchar(256);
declare @in varchar(256);
declare @pusage int;
declare @pstate bit;
declare @units varchar(256);
declare @mgmtip varchar(15);
declare @notes varchar(4096);
declare @color varchar(256);
declare @rack varchar(256);
declare @dc varchar(256);
declare @vendor varchar(256);*/
                cmd.Parameters.Add("@name", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@model", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@type", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@sn", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@in", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@pusage", SqlDbType.Int);//
                cmd.Parameters.Add("@pstate", SqlDbType.Bit);//
                cmd.Parameters.Add("@units", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@mgmtip", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@color", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@rack", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@dc", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@vendor", SqlDbType.VarChar).Size = 256;//
                cmd.Parameters.Add("@notes", SqlDbType.VarChar).Size = 4096;
                cmd.Parameters.Add("@responsible", SqlDbType.VarChar).Size = 512;
                cmd.Prepare();
                string line;
                bool first = true;
                while ((line = stream.ReadLine()) != null)
                {
                    line = line.Trim();
                    if (line.Length == 0) continue;
                    if (first) first = false;
                    else
                    {
                        //dc_name, rack_name, name, vendor, type, model, sn, in, pusage, pstate, units, ips, mgmtip, notes, color, responsible. "Dc_name", "rack_name", "name", "ips" and "units" are required.
                        // 0         1          2     3      4      5     6   7    8       9         10   11   12      13    14      15
                        var cols = line.Split(';');
                        cnt++;
                        if (cols[0].Length == 0) throw new Exception("Empty dc_name in string: " + line);
                        if (cols[1].Length == 0) throw new Exception("Empty rack_name in string: " + line);
                        if (cols[2].Length == 0) throw new Exception("Empty name in string: " + line);
                        if (cols[10].Length == 0) throw new Exception("Empty units in string: " + line);
                        //if (cols[11].Length == 0) throw new Exception("Empty IPs in string: " + line);
                        if (cols[10].Contains("-"))
                        {
                            var pair = cols[10].Split('-');
                            int start = 0;
                            int end = 0;
                            if (!int.TryParse(pair[0], out start)) throw new Exception("Error units format in string: " + line);
                            if (!int.TryParse(pair[1], out end)) throw new Exception("Error units format in string: " + line);
                            if (end <= start) throw new Exception("Error units format in string: " + line);
                        }
                        else
                        {
                            int units = 0;
                            if (!int.TryParse(cols[10], out units)) throw new Exception("Error units format in string: " + line);
                        }
                        var pstate = true;
                        if (cols[9].ToLower() == "off" || cols[9].ToLower() == "false" || cols[9] == "0") pstate = false;
                        
                        //dc_name, name, vendor, units, notes
                        cmd.Parameters["@dc"].Value = cols[0];
                        cmd.Parameters["@rack"].Value = cols[1];
                        cmd.Parameters["@name"].Value = cols[2];
                        if (cols[3].Length == 0) cmd.Parameters["@vendor"].Value = DBNull.Value;
                        else cmd.Parameters["@vendor"].Value = cols[3];
                        cmd.Parameters["@model"].Value = cols[5];
                        cmd.Parameters["@type"].Value = cols[4];
                        cmd.Parameters["@sn"].Value = cols[6];
                        cmd.Parameters["@in"].Value = cols[7];
                        if (cols[8].Length == 0) cmd.Parameters["@pusage"].Value = DBNull.Value;
                        else cmd.Parameters["@pusage"].Value = cols[8];
                        cmd.Parameters["@pstate"].Value = pstate;
                        cmd.Parameters["@units"].Value = cols[10];
                        cmd.Parameters["@mgmtip"].Value = cols[12];
                        cmd.Parameters["@color"].Value = cols[14];
                        cmd.Parameters["@notes"].Value = cols[13];
                        cmd.Parameters["@responsible"].Value = cols[15];
                        using (var rd = cmd.ExecuteReader())
                        {
                            if (rd.Read())
                            {
                                var result = rd[0].ToString();
                                if (result != "OK") throw new Exception(result);
                                var id = int.Parse(rd[1].ToString());
                                rd.Close();
                                if (cols[11].Length == 0)
                                {
                                    using (var icmd = conn.CreateCommand())
                                    {
                                        icmd.Transaction = trans;
                                        icmd.CommandText = "insert into server_ips (server_id, ip) values (@server_id, @ip)";
                                        icmd.Parameters.Add("@server_id", SqlDbType.Int).Value = id;
                                        icmd.Parameters.Add("@ip", SqlDbType.VarChar).Size = 20;
                                        icmd.Prepare();
                                        var ips = cols[11].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                                        foreach (var ip in ips)
                                        {
                                            icmd.Parameters["@ip"].Value = ip.Trim();
                                            icmd.ExecuteNonQuery();
                                        }
                                    }
                                }
                            }
                            else
                            {
                                throw new Exception("Unknown error in string " + line);
                            }
                        }
                    }
                }
            }
        }
        if (cnt == 0) throw new Exception("Empty servers file");
    }
</script>

<% ProcessRequest(); %>